As the global business climate becomes ever more competitive, survival in the marketplace requires timely and precise business decisions based on accurate and up-to-date information. Data analysis, reporting, and database query software provide business users with the tools to plow through the ever-growing mountain of data. Business intelligence (BI) is the name given to the broad category of applications and technologies for helping business users make better decisions. BI applications include decision support systems, query and reporting, online analytical processing (OLAP), statistical analysis, forecasting, and data mining. For decision support and OLAP applications, multidimensional databases are generally superior to traditional relational database management systems in terms of speed, size, and manageability. Specifically, multidimensional databases offer improvements in calculation performance, trend analysis/modeling, business modeling capabilities, and the management of sparse data sets. The structure of a multidimensional database is superior to a relational database for these applications because the sophisticated aggregation paths, calculations, and write-back capabilities can help model, more effectively, the business entities and relationships among entities that make up a company's operations. For example, companies usually organize products by lines or families, customers by regions or distribution channels, and employees by divisions and regions. Analysts use these structures to navigate in an intuitive manner.
Users of sophisticated BI systems may wish to extract data from one database, perform transformations of this data, and load the transformed data into another database. This process (referred to as “ETL”—Extraction, Transformation, and Loading) allows for the consolidation of data into a centralized data warehouse. This consolidated data may be used with one of the many database analysis, data mining, reporting and visualization tools. ETL is commonly used with a source database optimized for Online Transaction Processing (OLTP) and a destination database that is optimized to support analysis and reporting (e.g. star schema model or data warehouse). A data warehouse stores historical data and it is typically used as a read-only database for analysis and reporting. For example, data may be extracted from a data warehouse to load an OLAP model. During analysis, new data may be generated in OLAP systems based on historical data that was loaded into it. As a result, this calculated data exists only in the OLAP system and not in the original data warehouse. While it is known in the art to report on or extract a subset of data from an OLAP system for reporting, no simple method has been developed to extract data from an OLAP system for the purpose of loading it back into a data warehouse/relational system (which is typically a company's preferred historical record store).
There is a need for a method of extraction and transformation from a multidimensional model, and that can systematically load a variety of hierarchical structures in a relational model.